<?php


$config = parse_ini_file(realpath(dirname(__FILE__) . '/config/config.ini'));
$servername = $config['host'];
$username = $config['username'];
$password = $config['password'];
$dbname = $config['dbname'];
 
try {
    $conn = new PDO("mysql:host=$servername;", $username, $password);
    echo "连接成功"; 
    // 设置 PDO 错误模式为异常 
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

    $sql = "create database if not exists $dbname"; 
    // 使用 exec() ，因为没有结果返回 
    $conn->exec($sql); 

    //$sql = "drop TABLE $dbname.log IF exists"; 
    // 使用 exec() ，因为没有结果返回 
    //$conn->exec($sql); 

    // 使用 sql 创建来源数据表
    $sql = "CREATE TABLE if not exists $dbname.sources (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    name NVARCHAR(64) DEFAULT '',
    isValid INT DEFAULT 1,
    savetime DATETIME DEFAULT CURRENT_TIMESTAMP
    )";
 
    // 使用 exec() ，没有结果返回 
    $conn->exec($sql);


    // 使用 sql 创建日志数据表
    $sql = "CREATE TABLE if not exists $dbname.log (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    level NVARCHAR(32) NOT NULL,
    data NVARCHAR(2048) NOT NULL,
    source NVARCHAR(64) DEFAULT '',
    code NVARCHAR(256) DEFAULT '',
    clientip NVARCHAR(64) DEFAULT '',
    version NVARCHAR(32) DEFAULT '',
    time NVARCHAR(32) DEFAULT '',
    savetime DATETIME DEFAULT CURRENT_TIMESTAMP
    )";
    $conn->exec($sql);

    // 创建存储过程
    $sql = "CREATE PROCEDURE `$dbname`.`insertSourseIfNeedPro`(pname varchar(64))
BEGIN
    IF NOT EXISTS (SELECT 1 FROM db_monitor.sources WHERE name = pname) THEN 
        BEGIN
            INSERT INTO db_monitor.sources(name) VALUES(pname);
            -- INSERT INTO db_monitor.sources(name,id) VALUES(pname,(select tb.count from (select (count(name)+1) AS count from db_monitor.sources) tb));
        END;
    END IF;
END";
    $conn->exec($sql);

    echo "数据表 $dbname 创建成功";

}
catch(PDOException $e)
{
    echo $e->getMessage();
}

//连接在脚本执行完后会自动关闭。也可以使用以下代码来关闭连接：
$conn = null;
?>